// src/utils/sqlAstParser.js

import { Parser } from "node-sql-parser";

const parser = new Parser();

/**
 * 将 SQL 字符串解析为 updateDiagram 可用的数据结构
 * @param {string} sql SQL 字符串
 * @returns {Array} tables 数组
 */
export function parseSqlToDiagramTables(sql) {
  const ast = parser.astify(sql, { database: "MySQL" });
  const tables = Array.isArray(ast) ? ast : [ast];

  return tables
    .filter(stmt => stmt.type === "create")
    .map(table => {
      const tableName = table.table[0].table;
      const comment = extractComment(table.options);
      const columns = [];
      const primaryKeys = [];
      const foreignKeys = [];

      table.create_definitions.forEach(def => {
        if (def.resource === "column") {
          columns.push({
            name: def.column.column,
            comment: def.definition.comment
              ? `'${def.definition.comment.value}'`
              : ""
          });
        }

        if (def.constraint_type === "PRIMARY KEY") {
          primaryKeys.push(...def.definition.map(col => col.column));
        }

        if (def.constraint_type === "FOREIGN KEY") {
          const fkCols = def.definition.map(col => col.column);
          const refCols = def.reference.definition.map(col => col.column);
          const refTable = def.reference.table[0].table;
          const fkComment = def.comment?.value || "";

          fkCols.forEach((col, i) => {
            foreignKeys.push({
              column: col,
              referenceTable: refTable,
              referenceColumn: refCols[i] || refCols[0],
              comment: fkComment ? `'${fkComment}'` : ""
            });
          });
        }
      });

      return {
        tableName,
        comment: comment ? `'${comment}'` : "",
        columns,
        primaryKeys,
        foreignKeys
      };
    });
}
/**
 * 修复原始 SQL 字符串，使其适配 node-sql-parser 的 MySQL 语法要求
 * @param {string} rawSql - 原始 SQL 输入（多行、混乱、未格式化）
 * @returns {string} - 标准化后的 SQL 字符串
 */
export function fixMySqlForParser(sql) {
  return (
    sql
      // 替换列定义或约束中的 COMMENT（后面不能带 ;）
      .replace(/COMMENT\s+'([^']+)'(?=\s*[,\n)])/gi, "COMMENT = '$1'")

      // 替换表级 COMMENT（必须出现在结尾或 ) 后）
      .replace(/\)\s*COMMENT\s+'([^']+)'/gi, ") COMMENT = '$1'")

      // 确保每条 CREATE TABLE 语句结尾有分号
      .replace(/(?<!;)\s*$/, ";")

      // 清理重复分号
      .replace(/;{2,}/g, ";")

      // 移除行注释等
      .replace(/--.*$/gm, "")
      .trim()
  );
}
export function removeFkComments(sql) {
  return sql.replace(
    /(FOREIGN\s+KEY\s*\([^)]+\)\s+REFERENCES\s+\w+\s*\([^)]+\))\s+COMMENT\s*=\s*'[^']+'/gi,
    "$1"
  );
}
function extractComment(options = []) {
  const commentOption = options.find(opt => opt.option === "COMMENT");
  return commentOption?.value?.value || "";
}
